﻿<%@ WebHandler Language="C#" Class="RbAEOSDashBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 热表厂--SQCDP生产绩效看板
 * Copyright(c): 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.5.24
 */

public class RbAEOSDashBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getSafeSvgData":
                        r = getSafeSvgData(jObject);//查询s-svg数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getQualitySvgData":
                        r = getQualitySvgData(jObject);//查询q-svg数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCostSvgData":
                        r = getCostSvgData(jObject);//查询c-svg数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDeliverSvgData":
                        r = getDeliverSvgData(jObject);//查询d-svg数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPersonSvgData":
                        r = getPersonSvgData(jObject);//查询p-svg数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSChartData":
                        r = getSChartData(jObject);//查询s-折线图数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getQChartData":
                        r = getQChartData(jObject);//查询q-折线图数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCChartData":
                        r = getCChartData(jObject);//查询c-折线图数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDChartData":
                        r = getDChartData(jObject);//查询d-折线图数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPChartData":
                        r = getPChartData(jObject);//查询p-折线图数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getScrollBoardtData":
                        r = getScrollBoardtData(jObject);//查询轮播表数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSafeSvgDetail":
                        r = getSafeSvgDetail(jObject);//s-svg明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getQualitySvgDetail":
                        r = getQualitySvgDetail(jObject);//q-svg明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCostSvgDetail":
                        r = getCostSvgDetail(jObject);//c-svg明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDeliverSvgDetail":
                        r = getDeliverSvgDetail(jObject);//d-svg明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPersonSvgDetail":
                        r = getPersonSvgDetail(jObject);//p-svg明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSafeChartDetail":
                        r = getSafeSvgDetail(jObject);//s-折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getQualityChartDetail":
                        r = getQualityChartDetail(jObject);//q-折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getCostChartDetail":
                        r = getCostChartDetail(jObject);//c-折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDeliverChartDetail":
                        r = getDeliverChartDetail(jObject);//d-折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getPersonChartDetail":
                        r = getPersonChartDetail(jObject);//p-折线图明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSql(JObject jObject)
    {
        string sql = @"";
        return sql;
    }

    //查询s-svg数据
    Result getSafeSvgData(JObject jObject)
    {
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string orgtype = "";
        if (selectOrg.Count == 3)
        {
            orgtype = "班组级";
        }
        else if (selectOrg.Count == 2)
        {
            orgtype = "工区级";
        }
        else
        {
            orgtype = "分厂级";
        }

        string sql = @"
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) day,CASE WHEN COUNT(e.iswarning) > 0 THEN 1 ELSE 0 END iswarning
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN (
  --查 超过临界值 的指标
  SELECT t.createdate,t.threshold,t.itemname,t.determine,COUNT(t.id) qty,CASE WHEN COUNT(t.id) > t.threshold THEN 1 ELSE 0 END iswarning
  FROM
  (
    SELECT t.yearmonth,t.sqcdptype,t.itemname,t.orgtype,t.unit,t.threshold,t.determine,td.id,td.createdate
    FROM rbaeositem t
    INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
    WHERE t.determine = '大于' AND t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND t.sqcdptype = 'S'
  ) t
  HAVING COUNT(t.id) > t.threshold 
  GROUP BY t.createdate,t.threshold,t.itemname,t.determine
  --查 未达到预期值 的指标
  UNION ALL
  SELECT d.monthday createdate,t.threshold,t.itemname,t.determine,NVL(rd.qty,0) qty,CASE WHEN NVL(rd.qty,0) < t.threshold THEN 1 ELSE 0 END iswarning
  FROM 
  (
    SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
    FROM dual
    CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
  ) d
  LEFT JOIN rbaeositem t ON t.determine = '小于' AND t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND t.sqcdptype = 'S'
  LEFT JOIN (
    SELECT rd.createdate,t.itemname,COUNT(rd.id) qty
    FROM rbaeositemdetail rd
    LEFT JOIN rbaeositem t ON rd.rbaeositemid = t.id
    WHERE t.determine = '小于' AND t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND t.sqcdptype = 'S'
    GROUP BY rd.createdate,t.itemname
  ) rd ON rd.createdate = d.monthday AND rd.itemname = t.itemname
  WHERE NVL(rd.qty,0) < t.threshold
) e ON d.monthday = e.createdate
GROUP BY d.monthday
ORDER BY d.monthday
";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), orgtype);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询q-svg数据
    Result getQualitySvgData(JObject jObject)
    {
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string orgtype = "";
        if (selectOrg.Count == 3)
        {
            orgtype = "班组级";
        }
        else if (selectOrg.Count == 2)
        {
            orgtype = "工区级";
        }
        else
        {
            orgtype = "分厂级";
        }
        string sql = @"
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) day,CASE WHEN COUNT(e.isover) > 0 THEN 1 ELSE 0 END isover
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN (
  --产品返工次数
  SELECT t.createdate,t.threshold,t.itemname,COUNT(t.id) qty,CASE WHEN COUNT(t.id) > t.threshold THEN 1 ELSE 0 END isover
  FROM
  (
    SELECT t.yearmonth,t.sqcdptype,t.itemname,t.orgtype,t.unit,t.threshold,td.createdate,td.id
    FROM rbaeositem t
    INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
    WHERE t.sqcdptype = 'Q' AND t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' 
  ) t
  GROUP BY t.createdate,t.threshold,t.itemname
  HAVING COUNT(t.id) > t.threshold
  
  UNION ALL
  --一次交检验合格率(只查分厂级)
  SELECT n.createdate,n.threshold,n.itemname,n.qty,n.isover
  FROM 
  (
    SELECT mm.checkdate createdate,mm.threshold,mm.itemname,mm.containerqty qty,
         CASE WHEN mm.determine = '小于' THEN (CASE WHEN mm.passrate < mm.threshold THEN 1 ELSE 0 END)
              WHEN mm.determine = '大于' THEN (CASE WHEN mm.passrate > mm.threshold THEN 1 ELSE 0 END)
         END isover
    FROM 
    (
      SELECT t.checkdate,COUNT(t.containername) containerqty,SUM(DECODE(t.lossqty,0,0,1)) lossqty,
             COUNT(t.containername) - SUM(DECODE(t.lossqty,0,0,1)) passqty,
             ROUND((COUNT(t.containername) - SUM(DECODE(t.lossqty,0,0,1)))/COUNT(t.containername),2) * 100 passrate,
             rt.threshold,rt.unit,'一次交检合格率' itemname,rt.determine
      FROM
      (
        SELECT csd.containername,csd.workflowstepname,csd.dispatchqty,csd.reportqty,trunc(csd.checkdate) checkdate,csd.reportdate,csd.dispatchdate,
               csd.passqty,NVL(csd.lossqty,0) lossqty,csd.teamname,csd.workcentername,csd.usecompleted
        FROM containersteprecord csd
        LEFT JOIN factory f ON f.factoryid = csd.factoryid
        WHERE csd.checkdate >= TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') AND csd.checkdate <= LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) AND f.factoryname = '{2}'
      ) t
      INNER JOIN rbaeositem rt ON rt.itemname = '一次交检合格率' AND rt.status = 1 AND rt.yearmonth = '{0}' AND rt.sqcdptype = 'Q' AND rt.orgtype = '分厂级'
      GROUP BY t.checkdate,rt.threshold,rt.unit,rt.determine
    ) mm
   ) n
   WHERE n.isover > 0
) e ON d.monthday = e.createdate
GROUP BY d.monthday
ORDER BY d.monthday
";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), orgtype, jObject["factoryname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询p-svg数据
    Result getPersonSvgData(JObject jObject)
    {
        string sql = @"
--只统计到分厂级
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) day,CASE WHEN COUNT(e.iswarning) > 0 THEN 1 ELSE 0 END iswarning
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN 
(
  SELECT t.createdate,t.threshold,t.itemname,COUNT(t.id) qty,CASE WHEN COUNT(t.id) > t.threshold THEN 1 ELSE 0 END iswarning
  FROM
  (
    SELECT t.yearmonth,t.sqcdptype,t.itemname,t.orgtype,t.unit,t.threshold,t.determine,td.createdate,td.schedule,td.id,
           CASE WHEN td.schedule < t.threshold THEN 1 ELSE 0 END iswarning
    FROM rbaeositem t
    INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
    WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'P'
  ) t
  GROUP BY t.createdate,t.threshold,t.itemname
  HAVING SUM(t.iswarning) > 0
) e ON d.monthday = e.createdate
GROUP BY d.monthday
ORDER BY d.monthday";
        sql = string.Format(sql, jObject["yearmonth"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询c-svg数据
    Result getCostSvgData(JObject jObject)
    {
        string sql = @"
--在制品批次数，只统计分厂级
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) day,CASE WHEN COUNT(e.iswarning) > 0 THEN 1 ELSE 0 END iswarning
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN 
(
  SELECT d.monthday createdate,t.threshold,t.itemname,t.determine,NVL(m.qty,0) qty,
         CASE WHEN NVL(m.qty,0) > t.threshold THEN 1 ELSE 0 END iswarning
  FROM 
  (
    SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
    FROM dual
    CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
  ) d
  LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'C'
  LEFT JOIN 
  (
    SELECT t.createdate,COUNT(t.containername) qty
    FROM
    (
      SELECT TRUNC(c.originalstartdate) createdate,cci.containername
      FROM containercurrentinfo cci
      LEFT JOIN container c ON c.containerid = cci.containerid
      LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
      LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid
      WHERE TRUNC(c.originalstartdate) >= TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH')
            AND TRUNC(c.originalstartdate) <= LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) 
            AND csd.checkdate IS NULL
            AND f.factoryname = '{1}' 
    ) t
    GROUP BY t.createdate 
  ) m ON m.createdate = d.monthday
  WHERE NVL(m.qty,0) > t.threshold
) e ON d.monthday = e.createdate
GROUP BY d.monthday
ORDER BY d.monthday";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), jObject["factoryname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询d-svg数据
    Result getDeliverSvgData(JObject jObject)
    {
        string sql = @"
--计划按时完成率，看板只统计分厂级
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) day,CASE WHEN COUNT(e.iswarning) > 0 THEN 1 ELSE 0 END iswarning
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (to_date('{2}','yyyy-mm-dd') - to_date('{1}','yyyy-mm-dd') + 1)
) d
LEFT JOIN 
(
  SELECT d.monthday createdate,t.threshold,t.itemname,t.determine,m.intimefinishrate,
         CASE WHEN m.intimefinishrate IS NOT NULL AND m.intimefinishrate < t.threshold THEN 1 ELSE 0 END iswarning
  FROM 
  (
    SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
    FROM dual
    CONNECT BY ROWNUM <= (to_date('{2}','yyyy-mm-dd') - to_date('{1}','yyyy-mm-dd') + 1)
  ) d
  LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'D'
  INNER JOIN 
  (
      --计划按时完成率start
      SELECT t.plannedfinishdate,
             COUNT(t.workflowstepname) spectotal,
             SUM(CASE WHEN t.recivedate IS NOT NULL THEN 1 ELSE 0 END) finishSpecCount,
             SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END) intimeFinishSpecCount,
             SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays > 0 THEN 1 ELSE 0 END) delayFinishSpecCount,
             ROUND((SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END))/(COUNT(t.workflowstepname)),6) * 100 intimefinishrate
      FROM
      (
          SELECT f.factoryname fromfactory,f2.factoryname tofactory,w.workcentername toworkcentername,cci.productname,cci.productdescription,
                 cci.containername,cci.qty,csp.workflowstepname,cci.workflowstepname currentworkflowstepname,csp.stepplancreatedate,
                 csp.plannedstartdate,csp.plannedfinishdate,csd.senddate,csd.toacceptdate,e.fullname receiver,csr.completedate,csr.recivedate,
                 CASE WHEN csd.toacceptdate IS NOT NULL THEN TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate)
                      ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedstartdate)
                 END senddelaydays,
                 CASE WHEN csp.plannedfinishdate IS NOT NULL 
                      THEN CASE WHEN csr.recivedate IS NOT NULL THEN TRUNC(csr.recivedate) - TRUNC(csp.plannedfinishdate)
                            ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate) 
                           END 
                      ELSE 0 
                 END finishdelaydays    
          FROM containerstepplan csp
          LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
          LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
          LEFT JOIN factory f2 ON f2.factoryid = csp.specfactoryid
          LEFT JOIN mesreport_containership_table mt ON mt.containerid = csp.containerid AND mt.workflowstepname = csp.workflowstepname
          LEFT JOIN containershiprecord csd ON csd.containershiprecordid = mt.containershiprecordid AND csd.isuse = 0
          LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid
          LEFT JOIN workcenter w ON w.workcenterid = csd.toworkcenterid
          LEFT JOIN employee e ON e.employeeid = csd.ToAcceptor --热表接收人
          WHERE csp.plantype = 2 AND cci.containerstatus = 1 
                AND TRUNC(csp.plannedfinishdate) >= to_date('{1}','yyyy-mm-dd')
                AND TRUNC(csp.plannedfinishdate) <= to_date('{2}','yyyy-mm-dd')
                AND f2.factoryname = '{3}'
                --只查热表在计划发出时间当天接收、或在计划发出时间前1天接收的转工排产数据
                AND (TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = 0 OR TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = -1)
      ) t
      GROUP BY t.plannedfinishdate
      --计划按时完成率end

  ) m ON m.plannedfinishdate = d.monthday
  WHERE m.intimefinishrate < t.threshold
) e ON d.monthday = e.createdate
GROUP BY d.monthday
ORDER BY d.monthday";
        string startOfDate = jObject["startOfSelectMonth"].ToString();
        string endOfDate = "";
        if (jObject["yearmonth"].ToString() == jObject["thismonth"].ToString())
        {
            endOfDate = jObject["today"].ToString();
        }
        else
        {
            endOfDate = jObject["endOfSelectMonth"].ToString();
        }
        sql = string.Format(sql, jObject["yearmonth"].ToString(), startOfDate, endOfDate, jObject["factoryname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询s-chart数据
    Result getSChartData(JObject jObject)
    {
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string orgtype = "";
        if (selectOrg.Count == 3)
        {
            orgtype = "班组级";
        }
        else if (selectOrg.Count == 2)
        {
            orgtype = "工区级";
        }
        else
        {
            orgtype = "分厂级";
        }
        string sql = @"
SELECT d.monthday createdate,EXTRACT(DAY FROM d.monthday) day,NVL(rd.qty,0) qty
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
  FROM dual
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
--INNER JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND t.sqcdptype = 'S'
LEFT JOIN
(
  SELECT rd.createdate,COUNT(rd.id) qty
  FROM rbaeositemdetail rd
  INNER JOIN rbaeositem t ON rd.rbaeositemid = t.id
  WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND t.sqcdptype = 'S'
  GROUP BY rd.createdate
) rd ON rd.createdate = d.monthday
ORDER BY d.monthday";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), orgtype);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //q-折线图(一次交检合格率)
    Result getQChartData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday createdate,EXTRACT(DAY FROM d.monthday) day,NVL(rd.passrate,100) passrate
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
  FROM dual
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
INNER JOIN
(
  SELECT t.checkdate,COUNT(t.containername) containerqty,SUM(DECODE(t.lossqty,0,0,1)) lossqty,
         COUNT(t.containername) - SUM(DECODE(t.lossqty,0,0,1)) passqty,
         ROUND((COUNT(t.containername) - SUM(DECODE(t.lossqty,0,0,1)))/COUNT(t.containername),2) * 100 passrate
  FROM
  (
    SELECT csd.containername,csd.workflowstepname,csd.dispatchqty,csd.reportqty,trunc(csd.checkdate) checkdate,csd.reportdate,csd.dispatchdate,
           csd.passqty,NVL(csd.lossqty,0) lossqty,csd.teamname,csd.workcentername,csd.usecompleted
    FROM containersteprecord csd
    LEFT JOIN factory f ON f.factoryid = csd.factoryid
    WHERE csd.checkdate >= TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') 
          AND csd.checkdate <= LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) 
          AND f.factoryname = '{1}'
  ) t
  GROUP BY t.checkdate
) rd ON rd.checkdate = d.monthday
ORDER BY d.monthday";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), jObject["factoryname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //c-折线图
    Result getCChartData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday createdate,EXTRACT(DAY FROM d.monthday) day,NVL(rd.qty,0) qty
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
  FROM dual
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
INNER JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'C'
LEFT JOIN
(
  SELECT t.createdate,COUNT(t.containername) qty
  FROM
  (
    SELECT TRUNC(c.originalstartdate) createdate,cci.containername
    FROM containercurrentinfo cci
    LEFT JOIN container c ON c.containerid = cci.containerid
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid
    WHERE TRUNC(c.originalstartdate) >= TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH')
          AND TRUNC(c.originalstartdate) <= LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) 
          AND csd.checkdate IS NULL
          AND f.factoryname = '{1}' 
  ) t
  GROUP BY t.createdate
) rd ON rd.createdate = d.monthday
ORDER BY d.monthday";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), jObject["factoryname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //p-折线图
    Result getPChartData(JObject jObject)
    {
        string sql = @"
--只统计培训计划完成率
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) DAY,NVL(e.schedule,100) schedule
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
INNER JOIN 
(
  SELECT t.createdate,MAX(t.schedule) schedule
  FROM
  (
    SELECT td.createdate,td.schedule
    FROM rbaeositem t
    INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
    WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'P' AND t.itemname = '培训计划完成率'
  ) t
  GROUP BY t.createdate
) e ON d.monthday = e.createdate
GROUP BY d.monthday,e.schedule
ORDER BY d.monthday";
        sql = string.Format(sql, jObject["yearmonth"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //D折线图（计划按时完成率）
    Result getDChartData(JObject jObject)
    {
        string sql = @"
SELECT d.monthday,EXTRACT(DAY FROM d.monthday) DAY,NVL(e.intimefinishrate,100) intimefinishrate
FROM
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday 
  FROM dual 
  CONNECT BY ROWNUM <= (to_date('{2}','yyyy-mm-dd') - to_date('{1}','yyyy-mm-dd') + 1)
) d
INNER JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'D'
LEFT JOIN 
(
  --计划按时完成率start
  SELECT t.plannedfinishdate,ROUND((SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END))/(COUNT(t.workflowstepname)),4) * 100 intimefinishrate
  FROM
  (
      SELECT f.factoryname fromfactory,f2.factoryname tofactory,w.workcentername toworkcentername,cci.productname,cci.productdescription,
             cci.containername,cci.qty,csp.workflowstepname,cci.workflowstepname currentworkflowstepname,csp.stepplancreatedate,
             csp.plannedstartdate,csp.plannedfinishdate,csd.senddate,csd.toacceptdate,e.fullname receiver,csr.completedate,csr.recivedate,
             CASE WHEN csd.toacceptdate IS NOT NULL THEN TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate)
                  ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedstartdate)
             END senddelaydays,
             CASE WHEN csp.plannedfinishdate IS NOT NULL 
                  THEN CASE WHEN csr.recivedate IS NOT NULL THEN TRUNC(csr.recivedate) - TRUNC(csp.plannedfinishdate) ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate) END 
                  ELSE 0 
             END finishdelaydays    
      FROM containerstepplan csp
      LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
      LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
      LEFT JOIN factory f2 ON f2.factoryid = csp.specfactoryid
      LEFT JOIN mesreport_containership_table mt ON mt.containerid = csp.containerid AND mt.workflowstepname = csp.workflowstepname
      LEFT JOIN containershiprecord csd ON csd.containershiprecordid = mt.containershiprecordid AND csd.isuse = 0
      LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid
      LEFT JOIN workcenter w ON w.workcenterid = csd.toworkcenterid
      LEFT JOIN employee e ON e.employeeid = csd.ToAcceptor --热表接收人
      WHERE csp.plantype = 2 AND cci.containerstatus = 1
            AND TRUNC(csp.plannedfinishdate) >= to_date('{1}','yyyy-mm-dd')
            AND TRUNC(csp.plannedfinishdate) <= to_date('{2}','yyyy-mm-dd')
            AND f2.factoryname = '{3}'
            --只查热表在计划发出时间当天接收、或在计划发出时间前1天接收的转工排产数据
            AND (TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = 0 OR TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = -1)
  ) t
  GROUP BY t.plannedfinishdate
  --计划按时完成率end

) e ON d.monthday = e.plannedfinishdate
GROUP BY d.monthday,e.intimefinishrate
ORDER BY d.monthday";
        string startOfDate = jObject["startOfSelectMonth"].ToString();
        string endOfDate = "";
        if (jObject["yearmonth"].ToString() == jObject["thismonth"].ToString())
        {
            endOfDate = jObject["today"].ToString();
        }
        else
        {
            endOfDate = jObject["endOfSelectMonth"].ToString();
        }
        sql = string.Format(sql, jObject["yearmonth"].ToString(), startOfDate, endOfDate, jObject["factoryname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //轮播表数据
    Result getScrollBoardtData(JObject jObject)
    {
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string orgtype = "";
        if (selectOrg.Count == 3)
        {
            orgtype = "班组级";
        }
        else if (selectOrg.Count == 2)
        {
            orgtype = "工区级";
        }
        else
        {
            orgtype = "分厂级";
        }
        string sql = @"
SELECT t.sqcdptype,t.itemname,td.description,
       to_char(td.createdate,'yyyy/mm/dd') createdate,
       CASE WHEN td.needfinishdate IS NULL THEN '' ELSE to_char(td.needfinishdate,'yyyy/mm/dd') END needfinishdate,
       CASE WHEN td.schedule IS NOT NULL THEN td.schedule || '%' ELSE '' END schedule,
       td.dealemployee
FROM rbaeositem t
INNER JOIN rbaeositemdetail td ON td.rbaeositemid = t.id
WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}'";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), orgtype);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //s-svg明细
    Result getSafeSvgDetail(JObject jObject)
    {
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string orgtype = "";
        if (selectOrg.Count == 3)
        {
            orgtype = "班组级";
        }
        else if (selectOrg.Count == 2)
        {
            orgtype = "工区级";
        }
        else
        {
            orgtype = "分厂级";
        }
        string sql = @"
SELECT te.id,te.rbaeositemid,t.yearmonth,t.sqcdptype,t.itemname,t.itemnotes,t.orgtype,t.unit,t.threshold,t.determine,t.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       CASE WHEN t.itemname IN ('一次交检合格率','在制品批次数','计划按时完成率') THEN '1' ELSE '0' END disabled,
       te.description,te.improvemeasures,te.needfinishdate,te.finishdate,te.schedule,te.dealemployee,cci.containername,ws.workflowstepname,
       te.createdate,e.fullname creater,te.lastchangedate,e2.fullname updater
FROM rbaeositemdetail te
LEFT JOIN rbaeositem t ON t.id = te.rbaeositemid
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
LEFT JOIN employee e ON e.employeeid = te.createemployee
LEFT JOIN employee e2 ON e2.employeeid = te.lastchangeemployee
LEFT JOIN containercurrentinfo cci ON cci.containerid = te.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = te.workflowstepid
WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.sqcdptype = 'S' AND te.createdate = to_date('{1}','yyyy-mm-dd') AND t.orgtype = '{2}'
ORDER BY te.createdate";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), jObject["selectdate"].ToString(), orgtype);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //q-svg明细
    Result getQualitySvgDetail(JObject jObject)
    {
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        string orgtype = "";
        if (selectOrg.Count == 3)
        {
            orgtype = "班组级";
        }
        else if (selectOrg.Count == 2)
        {
            orgtype = "工区级";
        }
        else
        {
            orgtype = "分厂级";
        }
        string sql = @"
--一次交检合格率
SELECT ROUND((COUNT(t.containername) - SUM(DECODE(t.lossqty,0,0,1)))/COUNT(t.containername),2) * 100  passrate,
       '' ID,'' rbaeositemid,rt.yearmonth,rt.sqcdptype,rt.itemname,rt.itemnotes,rt.orgtype,rt.unit,rt.threshold,rt.determine,rt.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       NULL DESCRIPTION,NULL improvemeasures,NULL needfinishdate,NULL finishdate,NULL schedule,NULL dealemployee,NULL containername,NULL workflowstepname,
       TRUNC(t.checkdate) createdate,NULL creater,NULL lastchangedate,NULL updater
FROM
(
  SELECT csd.containername,csd.workflowstepname,csd.dispatchqty,csd.reportqty,trunc(csd.checkdate) checkdate,csd.reportdate,csd.dispatchdate,
         csd.passqty,NVL(csd.lossqty,0) lossqty,csd.teamname,csd.workcentername,csd.usecompleted
  FROM containersteprecord csd
  LEFT JOIN factory f ON f.factoryid = csd.factoryid
  WHERE csd.checkdate >= TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') 
        AND csd.checkdate <= LAST_DAY(TO_DATE('{0}', 'yyyy-mm'))
        AND f.factoryname = '{2}'
) t
INNER JOIN rbaeositem rt ON rt.itemname = '一次交检合格率' AND rt.status = 1 AND rt.yearmonth = '{0}' AND rt.sqcdptype = 'Q' AND rt.orgtype = '分厂级'
LEFT JOIN factory f ON f.factoryid = rt.factoryid
LEFT JOIN workcenter w ON w.workcenterid = rt.workcenterid
LEFT JOIN team ta ON ta.teamid = rt.teamid
WHERE TRUNC(t.checkdate) = to_date('{3}','yyyy-mm-dd')
HAVING SUM(DECODE(t.lossqty,0,0,1)) > 0
GROUP BY t.checkdate,rt.yearmonth,rt.sqcdptype,rt.itemname,rt.itemnotes,rt.orgtype,rt.unit,rt.threshold,rt.determine,rt.status,ta.teamname,f.factoryname,w.workcentername

--产品返工次数
UNION ALL
SELECT NULL passrate,te.id,te.rbaeositemid,t.yearmonth,t.sqcdptype,t.itemname,t.itemnotes,t.orgtype,t.unit,t.threshold,t.determine,t.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       te.description,te.improvemeasures,te.needfinishdate,te.finishdate,te.schedule,te.dealemployee,cci.containername,ws.workflowstepname,
       te.createdate,e.fullname creater,te.lastchangedate,e2.fullname updater
FROM rbaeositemdetail te
LEFT JOIN rbaeositem t ON t.id = te.rbaeositemid
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
LEFT JOIN employee e ON e.employeeid = te.createemployee
LEFT JOIN employee e2 ON e2.employeeid = te.lastchangeemployee
LEFT JOIN containercurrentinfo cci ON cci.containerid = te.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = te.workflowstepid
WHERE t.sqcdptype = 'Q' AND t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '{1}' AND te.createdate = to_date('{3}','yyyy-mm-dd')
";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), orgtype, jObject["factoryname"].ToString(), jObject["selectdate"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //c-svg明细
    Result getCostSvgDetail(JObject jObject)
    {
        string sql = @"
SELECT t.yearmonth,t.sqcdptype,t.threshold,t.itemname,t.unit,t.determine,NVL(m.qty,0) qty,d.monthday createdate
FROM 
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
  FROM dual
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'C'
LEFT JOIN 
(
  SELECT t.createdate,COUNT(t.containername) qty
  FROM
  (
    SELECT TRUNC(c.originalstartdate) createdate,cci.containername
    FROM containercurrentinfo cci
    LEFT JOIN container c ON c.containerid = cci.containerid
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid
    WHERE csd.checkdate IS NULL
          AND c.originalstartdate >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss')
          AND c.originalstartdate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')
          AND f.factoryname = '{2}'
  ) t
  GROUP BY t.createdate 
) m ON m.createdate = d.monthday
WHERE NVL(m.qty,0) > t.threshold";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), jObject["selectdate"].ToString(), jObject["factoryname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //d-svg明细
    Result getDeliverSvgDetail(JObject jObject)
    {
        string sql = @"
SELECT d.monthday createdate,t.yearmonth,t.sqcdptype,t.threshold,t.itemname,t.unit,t.determine,m.intimefinishrate
FROM 
(
  SELECT TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + (ROWNUM - 1) monthday
  FROM dual
  CONNECT BY ROWNUM <= (LAST_DAY(TO_DATE('{0}', 'yyyy-mm')) - TRUNC(TO_DATE('{0}', 'yyyy-mm'), 'MONTH') + 1)
) d
LEFT JOIN rbaeositem t ON t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'D'
INNER JOIN 
(
    --计划按时完成率start
    SELECT t.plannedfinishdate,
           COUNT(t.workflowstepname) spectotal,
           SUM(CASE WHEN t.recivedate IS NOT NULL THEN 1 ELSE 0 END) finishSpecCount,
           SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END) intimeFinishSpecCount,
           SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays > 0 THEN 1 ELSE 0 END) delayFinishSpecCount,
           ROUND((SUM(CASE WHEN t.recivedate IS NOT NULL AND t.finishdelaydays <= 0 THEN 1 ELSE 0 END))/(COUNT(t.workflowstepname)),6) * 100 intimefinishrate
    FROM
    (
        SELECT f.factoryname fromfactory,f2.factoryname tofactory,w.workcentername toworkcentername,cci.productname,cci.productdescription,
               cci.containername,cci.qty,csp.workflowstepname,cci.workflowstepname currentworkflowstepname,csp.stepplancreatedate,
               csp.plannedstartdate,csp.plannedfinishdate,csd.senddate,csd.toacceptdate,e.fullname receiver,csr.completedate,csr.recivedate,
               CASE WHEN csd.toacceptdate IS NOT NULL THEN TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate)
                    ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedstartdate)
               END senddelaydays,
               CASE WHEN csp.plannedfinishdate IS NOT NULL 
                    THEN CASE WHEN csr.recivedate IS NOT NULL THEN TRUNC(csr.recivedate) - TRUNC(csp.plannedfinishdate)
                              ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate) 
                         END 
                    ELSE 0 
               END finishdelaydays    
        FROM containerstepplan csp
        LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
        LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
        LEFT JOIN factory f2 ON f2.factoryid = csp.specfactoryid
        LEFT JOIN mesreport_containership_table mt ON mt.containerid = csp.containerid AND mt.workflowstepname = csp.workflowstepname
        LEFT JOIN containershiprecord csd ON csd.containershiprecordid = mt.containershiprecordid AND csd.isuse = 0
        LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid
        LEFT JOIN workcenter w ON w.workcenterid = csd.toworkcenterid
        LEFT JOIN employee e ON e.employeeid = csd.ToAcceptor --热表接收人
        WHERE csp.plantype = 2 AND cci.containerstatus = 1
              AND csp.plannedfinishdate = to_date('{1}','yyyy/mm/dd')
              AND f2.factoryname = '{2}'
              AND TRUNC(csp.plannedstartdate) >= TRUNC(csd.toacceptdate)
              --只查热表在计划发出时间当天接收、或在计划发出时间前1天接收的转工排产数据
              AND (TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = 0 OR TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = -1)
    ) t
    GROUP BY t.plannedfinishdate
    --计划按时完成率end
) m ON m.plannedfinishdate = d.monthday
WHERE m.intimefinishrate < t.threshold";

        sql = string.Format(sql, jObject["yearmonth"].ToString(), jObject["selectdate"].ToString(), jObject["factoryname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //p-svg明细
    Result getPersonSvgDetail(JObject jObject)
    {
        string sql = @"
SELECT te.id,te.rbaeositemid,t.yearmonth,t.sqcdptype,t.itemname,t.itemnotes,t.orgtype,t.unit,t.threshold,t.determine,t.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       te.description,te.improvemeasures,te.needfinishdate,te.finishdate,te.schedule,te.dealemployee,cci.containername,ws.workflowstepname,
       te.createdate,e.fullname creater,te.lastchangedate,e2.fullname updater
FROM rbaeositemdetail te
LEFT JOIN rbaeositem t ON t.id = te.rbaeositemid
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
LEFT JOIN employee e ON e.employeeid = te.createemployee
LEFT JOIN employee e2 ON e2.employeeid = te.lastchangeemployee
LEFT JOIN containercurrentinfo cci ON cci.containerid = te.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = te.workflowstepid
WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'P' AND te.createdate = to_date('{1}','yyyy-mm-dd')
      AND te.schedule < t.threshold";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), jObject["selectdate"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //一次交检合格率明细
    Result getQualityChartDetail(JObject jObject)
    {
        string sql = @"
SELECT csd.containername,csd.workflowstepname,csd.workcentername,csd.teamname,
       csd.dispatchqty,csd.dispatchemployeename,csd.dispatchdate,
       csd.reportqty,csd.reportemployeename,csd.reportdate,
       csd.checkemployeename,trunc(csd.checkdate) checkdate,
       csd.passqty,
       NVL(csd.lossqty,0) lossqty
FROM containersteprecord csd
LEFT JOIN containercurrentinfo cci ON cci.containerid = csd.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
WHERE NVL(csd.lossqty,0) > 0
      AND csd.checkdate >= to_date('{0} 00:00:00','yyyy/mm/dd hh24:mi:ss')
      AND csd.checkdate <= to_date('{0} 23:59:59','yyyy/mm/dd hh24:mi:ss')
      AND f.factoryname = '{1}'";

        sql = string.Format(sql, jObject["selectdate"].ToString(), jObject["factoryname"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //c-折线图明细
    Result getCostChartDetail(JObject jObject)
    {
        string sql = @"
SELECT c.originalstartdate,cci.containername,cci.qty,cci.workflowstepname,cci.workcentername,cci.teamname
FROM containercurrentinfo cci
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid
WHERE f.factoryname = '{0}' 
      AND c.originalstartdate >= to_date('{1} 00:00:00','yyyy/mm/dd hh24:mi:ss')
      AND c.originalstartdate <= to_date('{1} 23:59:59','yyyy/mm/dd hh24:mi:ss')        
      AND csd.checkdate IS NULL ";
        sql = string.Format(sql, jObject["factoryname"].ToString(), jObject["selectdate"].ToString());
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //d-折线图明细
    Result getDeliverChartDetail(JObject jObject)
    {
        string sql = @"
SELECT t.*
FROM (
    SELECT f.factoryname fromfactory,f2.factoryname tofactory,w.workcentername toworkcentername,cci.productname,cci.productdescription,
           cci.containername,cci.qty,csp.workflowstepname,cci.workflowstepname currentworkflowstepname,csp.stepplancreatedate,
           csp.plannedstartdate,csp.plannedfinishdate,csd.senddate,csd.toacceptdate,e.fullname receiver,csr.completedate,csr.recivedate,
           CASE WHEN csd.toacceptdate IS NOT NULL THEN TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate)
                ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedstartdate)
           END senddelaydays,
           CASE WHEN csp.plannedfinishdate IS NOT NULL 
                THEN CASE WHEN csr.recivedate IS NOT NULL THEN TRUNC(csr.recivedate) - TRUNC(csp.plannedfinishdate) ELSE TRUNC(SYSDATE) - TRUNC(csp.plannedfinishdate) END 
                ELSE 0 
           END finishdelaydays    
    FROM containerstepplan csp
    LEFT JOIN containercurrentinfo cci ON cci.containerid = csp.containerid
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN factory f2 ON f2.factoryid = csp.specfactoryid
    LEFT JOIN mesreport_containership_table mt ON mt.containerid = csp.containerid AND mt.workflowstepname = csp.workflowstepname
    LEFT JOIN containershiprecord csd ON csd.containershiprecordid = mt.containershiprecordid AND csd.isuse = 0
    LEFT JOIN containershiprecive csr ON csr.containershiprecordid = csd.containershiprecordid
    LEFT JOIN workcenter w ON w.workcenterid = csd.toworkcenterid
    LEFT JOIN employee e ON e.employeeid = csd.ToAcceptor --热表接收人
    WHERE csp.plantype = 2 AND cci.containerstatus = 1
          AND f2.factoryname = '{0}'
          AND csp.plannedfinishdate = to_date('{1}','yyyy/mm/dd')
          --只查热表在计划发出时间当天接收、或在计划发出时间前1天接收的转工排产数据
          AND (TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = 0 OR TRUNC(csd.toacceptdate) - TRUNC(csp.plannedstartdate) = -1)
) t
WHERE t.finishdelaydays > 0";
        sql = string.Format(sql, jObject["factoryname"].ToString(), jObject["selectdate"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //p-折线图明细
    Result getPersonChartDetail(JObject jObject)
    {
        string sql = @"
SELECT te.id,te.rbaeositemid,t.yearmonth,t.sqcdptype,t.itemname,t.itemnotes,t.orgtype,t.unit,t.threshold,t.determine,t.status,
       CASE WHEN ta.teamname IS NOT NULL THEN f.factoryname || '/' || w.workcentername || '/' || ta.teamname
            WHEN ta.teamname IS NULL AND w.workcentername IS NOT NULL THEN f.factoryname || '/' || w.workcentername
            ELSE f.factoryname
       END orginfo,
       te.description,te.improvemeasures,te.needfinishdate,te.finishdate,te.schedule,te.dealemployee,cci.containername,ws.workflowstepname,
       te.createdate,e.fullname creater,te.lastchangedate,e2.fullname updater
FROM rbaeositemdetail te
LEFT JOIN rbaeositem t ON t.id = te.rbaeositemid
LEFT JOIN factory f ON f.factoryid = t.factoryid
LEFT JOIN workcenter w ON w.workcenterid = t.workcenterid
LEFT JOIN team ta ON ta.teamid = t.teamid
LEFT JOIN employee e ON e.employeeid = te.createemployee
LEFT JOIN employee e2 ON e2.employeeid = te.lastchangeemployee
LEFT JOIN containercurrentinfo cci ON cci.containerid = te.containerid
LEFT JOIN workflowstep ws ON ws.workflowstepid = te.workflowstepid
WHERE t.status = 1 AND t.yearmonth = '{0}' AND t.orgtype = '分厂级' AND t.sqcdptype = 'P' AND t.itemname = '培训计划完成率' AND te.createdate = to_date('{1}','yyyy-mm-dd')
ORDER BY te.createdate";
        sql = string.Format(sql, jObject["yearmonth"].ToString(), jObject["selectdate"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }
}